﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Configuration;

namespace Core
{
    internal class SqlServerOrganizationStorage:IOrganizationStorage
    {
        string m_ConnectionString = "";
        public SqlServerOrganizationStorage()
        {
            Configuration config = WebConfigurationManager.OpenWebConfiguration(
                HttpContext.Current.Request.ApplicationPath == "/" ? "/Lesktop" : HttpContext.Current.Request.ApplicationPath + "/Lesktop"
            );

            m_ConnectionString = config.ConnectionStrings.ConnectionStrings["Lesktop_ConnectString"].ConnectionString;
            //m_ConnectionString = @"Data Source=.;Initial Catalog=YBIM2011;Persist Security Info=True;User ID=sa;Password=123456";
        }

        /// <summary>
        /// 添加部门
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pdid"></param>
        /// <param name="cindex"></param>
        void IOrganizationStorage.AddDept(string name, long pdid, long cindex)
        {
            string sql = "INSERT INTO DIMDEPT(dname,pdid,cindex)values(@dname,@pdid,@cindex)";
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.Parameters.Add("dname", DbType.String).Value = name;
                cmd.Parameters.Add("pdid", DbType.Int64).Value = pdid;
                cmd.Parameters.Add("cindex", DbType.Int64).Value = cindex;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        void IOrganizationStorage.DeleteDept(long did)
        {
            string sql = "DELETE DIMDEPT WHERE did=@did ;  DELETE DIMUDD WHERE did=@did ";
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.Parameters.Add("did", DbType.Int64).Value = did;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        DataTable IOrganizationStorage.GetAllDepts()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT CAST(did AS NVARCHAR) AS ID,pdid AS ParentId,dname as Name\r\n                                FROM DIMDEPT order by cindex asc ;";
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        DataTable IOrganizationStorage.GetCompanyInfo()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT Id,Name,Tel,Address,Logo FROM DIMCompany ;";
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        DataRowCollection IOrganizationStorage.GetDeptAllUser(string deptId)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    cmd.Connection = conn;
                    string sql = string.Format("SELECT [Key],Name FROM dbo.DIMUsers WHERE [KEY] IN (SELECT uid FROM dbo.DIMUDD WHERE did IN( SELECT * FROM dbo.FUN_GetChildList('DIMDEPT','{0}')))", deptId);
                    cmd.CommandText = sql;
                    SqlDataAdapter ada = new SqlDataAdapter();
                    ada.SelectCommand = cmd;
                    ada.Fill(dt);
                    ada.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt.Rows;
        }

        DataTable IOrganizationStorage.GetDeptById(long did)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = string.Format("SELECT did,dname,cindex,pdid FROM DIMDEPT WHERE did={0}", did);
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        DataTable IOrganizationStorage.GetDeptList(string filter)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = string.Format("SELECT a.did,a.dname,a.cindex,a.dname,a.pdid,ISNULL(b.dname,'') AS parentname \r\n                      FROM DIMDEPT a LEFT JOIN DIMDEPT b ON a.did=b.pdid WHERE 1=1 {0} ORDER BY a.cindex;", filter);
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }
        DataRowCollection IOrganizationStorage.GetDepts()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    cmd.Connection = conn;
                    cmd.CommandText = @"SELECT b.[key] as uid,a.did AS parentid,b.Name,'1' AS dtype,b.EMail,b.AcceptStrangerIM,b.DiskSize,b.HeadIMG,
b.HomePage,b.InviteCode,b.IsTemp,b.MsgFileLimit,b.MsgImageLimit,b.Nickname,b.RegisterTime,b.Remark,
b.Type,b.UpperName,b.password,d.USER_PHONE as phone,d.USER_PHONE as telphone,d.USER_PHONE as mobilephone,-1 AS cindex 
FROM DeptMapping a,Users b,aos_department c,aos_user d
where a.deptid=c.DEPARTMENT_ID 
and c.DEPARTMENT_ID=d.DEPARTMENT_ID
and b.name=d.LOGIN_NAME
UNION ALL
SELECT CAST(did AS NVARCHAR) AS did,
pdid AS parentid,b.DEPARTMENT_NAME as dname,'0' AS dtype,'' as EMail,'' as AcceptStrangerIM,'0' as DiskSize,'' as HeadIMG,'' as HomePage,
'' as InviteCode,'' as IsTemp,'0' as MsgFileLimit,'0' as MsgImageLimit,b.DEPARTMENT_NAME as Nickname,
GETDATE() as RegisterTime,'' as Remark,'0' as Type,'' as UpperName,''as password,'' as phone,
'' as telphone,'' as mobilephone,cindex
FROM deptmapping a,aos_department b
where a.deptid=b.department_id
 order by cindex asc ";
                    //cmd.CommandText = "SELECT a.uid,a.did AS parentid,b.Name,'1' AS dtype,b.EMail,b.AcceptStrangerIM,b.DiskSize,b.HeadIMG,\r\n                                b.HomePage,b.InviteCode,b.IsTemp,b.MsgFileLimit,b.MsgImageLimit,b.Nickname,b.RegisterTime,b.Remark,\r\n                                b.Type,b.UpperName,b.password,b.phone,b.telphone,b.mobilephone,-1 AS cindex\r\n                                FROM DIMUDD a \r\n                                  LEFT JOIN DIMUsers b ON a.uid=b.[key]\r\n                                UNION ALL\r\n                                SELECT CAST(did AS NVARCHAR) AS did,pdid AS parentid,dname,'0' AS dtype,'' as EMail,'' as AcceptStrangerIM\r\n                                       ,'0' as DiskSize,'' as HeadIMG,'' as HomePage,'' as InviteCode,'' as IsTemp,'0' as MsgFileLimit,'0' as MsgImageLimit,\r\n                                       dname as Nickname,GETDATE() as RegisterTime,'' as Remark,'0' as Type,'' as UpperName,''as password,'' as phone,'' as telphone,'' as mobilephone,cindex \r\n                                FROM DIMDEPT order by cindex asc ;";
                    SqlDataAdapter ada = new SqlDataAdapter();
                    ada.SelectCommand = cmd;
                    ada.Fill(dt);
                    ada.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt.Rows;
        }

        DataRowCollection IOrganizationStorage.GetDepts(string user)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    cmd.Connection = conn;
                    cmd.CommandText = @"SELECT b.[key] as uid,a.did AS parentid,b.Name,'1' AS dtype,b.EMail,b.AcceptStrangerIM,b.DiskSize,b.HeadIMG,
b.HomePage,b.InviteCode,b.IsTemp,b.MsgFileLimit,b.MsgImageLimit,b.Nickname,b.RegisterTime,b.Remark,
b.Type,b.UpperName,b.password,d.USER_PHONE as phone,d.USER_PHONE as telphone,d.USER_PHONE as mobilephone,-1 AS cindex 
FROM DeptMapping a,Users b,aos_department c,aos_user d
where a.deptid=c.DEPARTMENT_ID 
and c.DEPARTMENT_ID=d.DEPARTMENT_ID
and b.name=d.LOGIN_NAME
and b.name!='"+ user +"'";
                    cmd.CommandText += @"  UNION ALL
SELECT CAST(did AS NVARCHAR) AS did,
pdid AS parentid,b.DEPARTMENT_NAME as dname,'0' AS dtype,'' as EMail,'' as AcceptStrangerIM,'0' as DiskSize,'' as HeadIMG,'' as HomePage,
'' as InviteCode,'' as IsTemp,'0' as MsgFileLimit,'0' as MsgImageLimit,b.DEPARTMENT_NAME as Nickname,
GETDATE() as RegisterTime,'' as Remark,'0' as Type,'' as UpperName,''as password,'' as phone,
'' as telphone,'' as mobilephone,cindex
FROM deptmapping a,aos_department b
where a.deptid=b.department_id
 order by cindex asc ";
                    //cmd.CommandText = "SELECT a.uid,a.did AS parentid,b.Name,'1' AS dtype,b.EMail,b.AcceptStrangerIM,b.DiskSize,b.HeadIMG,\r\n                                b.HomePage,b.InviteCode,b.IsTemp,b.MsgFileLimit,b.MsgImageLimit,b.Nickname,b.RegisterTime,b.Remark,\r\n                                b.Type,b.UpperName,b.password,b.phone,b.telphone,b.mobilephone,-1 AS cindex\r\n                                FROM DIMUDD a \r\n                                  LEFT JOIN DIMUsers b ON a.uid=b.[key]\r\n                                UNION ALL\r\n                                SELECT CAST(did AS NVARCHAR) AS did,pdid AS parentid,dname,'0' AS dtype,'' as EMail,'' as AcceptStrangerIM\r\n                                       ,'0' as DiskSize,'' as HeadIMG,'' as HomePage,'' as InviteCode,'' as IsTemp,'0' as MsgFileLimit,'0' as MsgImageLimit,\r\n                                       dname as Nickname,GETDATE() as RegisterTime,'' as Remark,'0' as Type,'' as UpperName,''as password,'' as phone,'' as telphone,'' as mobilephone,cindex \r\n                                FROM DIMDEPT order by cindex asc ;";
                    SqlDataAdapter ada = new SqlDataAdapter();
                    ada.SelectCommand = cmd;
                    ada.Fill(dt);
                    ada.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt.Rows;
        }

        DataTable IOrganizationStorage.GetUsersByDeptId(long did)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = string.Format("SELECT [Key],Name,Nickname FROM DIMUsers WHERE  [type]=0 AND  [Key] IN(SELECT uid FROM dbo.DIMUDD WHERE did={0})", did);
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        DataTable IOrganizationStorage.GetUsersByNoExistsDept(long did)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandText = string.Format("SELECT [Key],Name,Nickname FROM DIMUsers WHERE  [type]=0 AND [Key] NOT IN(SELECT uid FROM dbo.DIMUDD WHERE did={0}) ", did);
                        SqlDataAdapter ada = new SqlDataAdapter();
                        ada.SelectCommand = cmd;
                        ada.Fill(dt);
                        ada.Dispose();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    return dt;
                }
                finally
                {
                    conn.Close();
                }
            }
            return dt;
        }

        void IOrganizationStorage.UpdateCompanyInfo(string id, string name, string tel, string address, string logo)
        {
            string sql = "";
            if (id == "")
            {
                sql = "INSERT INTO DIMCompany(name,tel,address,logo)VALUES(@name,@tel,@address,@logo)";
            }
            else
            {
                sql = "UPDATE DIMCompany SET name=@name,tel=@tel,address=@address,logo=@logo where id=@id";
            }
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                if (id != "")
                {
                    cmd.Parameters.Add("id", DbType.String).Value = id;
                }
                cmd.Parameters.Add("name", DbType.String).Value = name;
                cmd.Parameters.Add("tel", DbType.String).Value = tel;
                cmd.Parameters.Add("address", DbType.String).Value = address;
                cmd.Parameters.Add("logo", DbType.String).Value = logo;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        void IOrganizationStorage.UpdateDept(long did, string name, long pdid, long cindex)
        {
            string sql = "UPDATE DIMDEPT SET dname=@dname,pdid=@pdid,cindex=@cindex WHERE did=@did";
            using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.Parameters.Add("dname", DbType.String).Value = name;
                cmd.Parameters.Add("did", DbType.Int64).Value = did;
                cmd.Parameters.Add("pdid", DbType.Int64).Value = pdid;
                cmd.Parameters.Add("cindex", DbType.Int64).Value = cindex;
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        void IOrganizationStorage.UpdateDeptMember(string ids, long did)
        {
            StringBuilder sql = new StringBuilder();
            foreach (string id in ids.Split(new char[] { ',' }))
            {
                if (id != "")
                {
                    sql.AppendFormat("DELETE FROM DIMUDD WHERE did={0} AND uid={1} ; INSERT INTO DIMUDD(did,uid,tid)VALUES({0},{1},0) ", did, id);
                }
            }
            if (sql.Length > 0)
            {
                using (SqlConnection conn = new SqlConnection(this.m_ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql.ToString();
                    conn.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }


    }
}
